package com.vizhuo.utils;

import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Map;
import java.util.Properties;
import java.util.Set;

import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;

public class DataBaseUtil {
    /**
     * 配置文件位置
     */
    public static String filePath = DataBaseUtil.class.getResource("/").getPath().substring(1) + "config.properties";

    /**
     * 数据库连接属性
     */
    private static String url;

    private static String username;

    private static String password;

    /**
     * 获得数据库连接参数
     * 
     * @param 参数名称
     *            ，jdbc.url、jdbc.username、jdbc.password
     * @return
     */
    public static String getDBString(String strName) {

        // 配置文件路径+名称
        Properties props = new Properties();
        InputStream in = null;
        try {
            in = new BufferedInputStream(new FileInputStream(filePath));
            props.load(in);
            String value = props.getProperty(strName);
            System.out.println("获取数据库配置[" + strName + "]:'" + value + "'");
            return value;
        } catch (Exception e) {
            System.out.println("获取数据库配置失败");
            e.printStackTrace();
            return null;
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }

    /** 连接数据库的URL */
    // private final String url = "jdbc:mysql://10.0.0.188:3306/xinjiang";
    /** 指定数据的用户名和密码 */
    // private final String username = "telSalesLottery";
    // private final String password = "telSalesLottery";
    /** 定义一个int记录更新的记录数量 */
    /** 定义一个结果集 用于放回查询结果 */
    Connection conn = null;

    ResultSet resultSet = null;

    PreparedStatement pstmt = null;

    /** 关闭连接 */
    public boolean coles() {
        boolean isColes = false;
        if (resultSet != null) {
            try {
                resultSet.close();
                resultSet = null;
                isColes = true;
            } catch (SQLException e) {
                isColes = false;
                e.printStackTrace();
                System.out.println("关闭结果集发生错误");
            }
        }
        if (pstmt != null) {
            try {
                pstmt.close();
                pstmt = null;
                isColes = true;
            } catch (SQLException e) {
                isColes = false;
                e.printStackTrace();
                System.out.println("关闭pstmt发生异常");
            }
        }
        if (conn != null) {
            try {
                conn.close();
                conn = null;
                isColes = true;
            } catch (Exception e) {
                isColes = false;
                e.printStackTrace();
                System.out.println("关闭conn发生异常");
            }
        }
        return isColes;
    }

    public static void main(String[] args) throws Exception {
        DataBaseUtil dbu = new DataBaseUtil();
        String str = dbu.test();
        // System.out.println(str);
        dbu.coles();
        Map m = JsonUtil.getMap4Json(str);
        // System.out.println(m);
        String data = m.get("data").toString();
        // System.out.println(data);
        Map dataMap = JsonUtil.getMap4Json(data);
        // System.out.println(dataMap);
        Set keys = dataMap.keySet();
        Object[] keyArray = keys.toArray();
        for (int i = 0; i < keyArray.length; i++) {
            Object key = (String) keyArray[i];
            JSONObject jsobj = (JSONObject) dataMap.get(key);
            String tmpJson = jsobj.toString();
            Map<String, String> tmpMap = JsonUtil.getMap4Json(tmpJson);
            // System.out.println("-----------------------------");
            // System.out.println(tmpMap);
            System.out.println("cityMap.put(\"" + key + "\", new PeccancyCity(\"" + key + "\", \""
                    + tmpMap.get("pinyin") + "\", " + tmpMap.get("frameno") + ", " + tmpMap.get("engineno") + ", "
                    + tmpMap.get("imgcode") + ", \"" + tmpMap.get("province") + "\", \"" + tmpMap.get("city")
                    + "\", \"" + tmpMap.get("lsprefix") + "\", \"" + tmpMap.get("lsnum") + "\"));");
        }
    }

    public String test() throws Exception {
        initProperties();
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, username, password);
        if (conn == null) {
            System.out.println("建立数据库发生错误！");
        }
        System.out.println("连接数据库成功");
        String sql = "select json from test where id = 1";
        pstmt = conn.prepareStatement(sql);
        resultSet = pstmt.executeQuery();
        String str = null;
        if (resultSet.next()) {
            str = resultSet.getString(1);
            // System.out.println(str);

        }
        return str;
    }

    public static void initProperties() {
        if (StringUtils.isBlank(url)) {
            url = getDBString("jdbc_url");
        }

        if (StringUtils.isBlank(username)) {
            username = getDBString("jdbc_username");
        }

        if (password == null) {
            // passwor有可能是''（空字符），所以判断方式与其他不同
            password = getDBString("jdbc_password");
        }
    }
}
